// DBHelper.java
package com.example.orderfood2.DBHelper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "Mysql.db";
    private static final int DATABASE_VERSION = 3;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
//        // 创建manager表
//        String createManagerTableQuery = "CREATE TABLE manager(" +
//                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
//                "username TEXT NOT NULL," +
//                "password TEXT NOT NULL" +
//                ")";
//        db.execSQL(createManagerTableQuery);
//
//        // 创建teacher表
//        String createTeacherTableQuery = "CREATE TABLE teacher(" +
//                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
//                "username TEXT NOT NULL," +
//                "password TEXT NOT NULL," +
//                "sex TEXT NOT NULL," +
//                "title TEXT NOT NULL," +
//                "date TEXT NOT NULL," +
//                "science TEXT NOT NULL" +
//                ")";
//        db.execSQL(createTeacherTableQuery);
//
//        // 创建student表
//        String createStudentTableQuery = "CREATE TABLE student (" +
//                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
//                "username TEXT NOT NULL," +
//                "password TEXT NOT NULL," +
//                "sex TEXT NOT NULL," +
//                "classId TEXT NOT NULL," +
//                "score TEXT NOT NULL," +
//                "date TEXT NOT NULL," +
//                "enrol TEXT NOT NULL" +
//                ")";
//        db.execSQL(createStudentTableQuery);
//
//        // 创建class_info表
//        String createClassInfoTableQuery = "CREATE TABLE class_info(" +
//                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
//                "classnumber TEXT NOT NULL," +
//                "classname TEXT NOT NULL" +
//                ")";
//        db.execSQL(createClassInfoTableQuery);
        createOriginalTables(db);
        createNewTables(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 3) {
            createNewTables(db);
        }
    }
//    / 创建原有表的方法
    private void createOriginalTables(SQLiteDatabase db) {
        // 创建manager表
        String createManagerTableQuery = "CREATE TABLE IF NOT EXISTS manager(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL" +
                ")";
        db.execSQL(createManagerTableQuery);

        // 创建teacher表
        String createTeacherTableQuery = "CREATE TABLE IF NOT EXISTS teacher(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL," +
                "sex TEXT NOT NULL," +
                "title TEXT NOT NULL," +
                "date TEXT NOT NULL," +
                "science TEXT NOT NULL" +
                ")";
        db.execSQL(createTeacherTableQuery);

        // 创建student表
        String createStudentTableQuery = "CREATE TABLE IF NOT EXISTS student (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "username TEXT NOT NULL," +
                "password TEXT NOT NULL," +
                "sex TEXT NOT NULL," +
                "classId TEXT NOT NULL," +
                "score TEXT NOT NULL," +
                "date TEXT NOT NULL," +
                "enrol TEXT NOT NULL" +
                ")";
        db.execSQL(createStudentTableQuery);

        // 创建class_info表
        String createClassInfoTableQuery = "CREATE TABLE IF NOT EXISTS class_info(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "classnumber TEXT NOT NULL," +
                "classname TEXT NOT NULL" +
                ")";
        db.execSQL(createClassInfoTableQuery);
    }

    // 创建新增表的方法
    private void createNewTables(SQLiteDatabase db) {
        // 1. 创建time_table表（课程表）
        String createTimeTableQuery = "CREATE TABLE IF NOT EXISTS time_table(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "student_id INTEGER NOT NULL," +
                "first_class TEXT," +
                "second_class TEXT," +
                "third_class TEXT," +
                "fourth_class TEXT," +
                "fifth_class TEXT" +
                ")";
        db.execSQL(createTimeTableQuery);

        // 2. 创建school_new表（校园新闻表）
        String createSchoolNewTableQuery = "CREATE TABLE IF NOT EXISTS school_new(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "title TEXT NOT NULL," +
                "content TEXT NOT NULL" +
                ")";
        db.execSQL(createSchoolNewTableQuery);

        // 3. 创建student_avatar表（学生头像表）
        String createStudentAvatarTableQuery = "CREATE TABLE IF NOT EXISTS student_avatar(" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "student_id INTEGER NOT NULL," +
                "avatar BLOB" + // BLOB类型用于存储二进制数据（图片）
                ")";
        db.execSQL(createStudentAvatarTableQuery);
    }
}